# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql


class TaobaoPipeline(object):
    def process_item(self, item, spider):
        return item


class TaobaoMysqlPipeline(object):
    def __init__(self):
        self.db = pymysql.connect('127.0.0.1', 'root', '1996010207', 'taobao', charset='utf8')
        self.cursor = self.db.cursor()

    def process_item(self, item, spider):
        url = "'{}'".format(item['url'])
        boo, url_id = self.__commodity_inspection(url)
        primary = [item['title'], item['shop'], item['shop_owner'], item['url']]
        child = [item['price'], item['time']]
        try:
            if boo:
                for i in child[0]:
                    self.__child_table(url_id, i[2], child[1], i[0], i[1])
            else:
                self.__primary_table(primary, child)
            return item
        except Exception as e:
            print(e)
            self.db.rollback()

    def __commodity_inspection(self, url):
        """
        判断主要数据库中是否有这一件商品，如果有返回true与其ID，若无返回false与-1
        :param url: 查询的条件url
        :return:
        """
        select = "select id from commodity where url={}".format(url)
        result_num = self.cursor.execute(select)
        if result_num >= 1:
            result_id = self.cursor.fetchall()[0][0]
            return True, result_id
        else:
            return False, -1

    def __child_table(self, pid, price, time, sort1="", sort2=""):
        """
        向子数据表中插入数据
        :param pid: 外键id
        :param price: 价格
        :param time: 时间
        :return:
        """
        sql = "insert into price (id, sort1,sort2,price, date) values (%s,%s,%s,%s,%s)"
        self.cursor.execute(sql, [pid, sort1, sort2, price, time])
        self.db.commit()

    def __primary_table(self, primary, child):
        """
        向主表中插入数据，当第一次存入这个商品时候，需要调用这个方法
        :param primary: 主表需要的数据
        :param child: 外键表需要的数据
        :return:
        """
        sql = "insert into commodity (title, shop, shop_owner, url) values (%s,%s,%s,%s)"
        self.cursor.execute(sql, primary)
        self.db.commit()
        url = "'{}'".format(primary[-1])
        boo, result_id = self.__commodity_inspection(url)
        if boo:
            for i in child[0]:
                self.__child_table(result_id, i[2], child[1], i[0], i[1])

    def close_spider(self, spider):
        self.cursor.close()
        self.db.close()
